﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace auxiliary_proj
{
    public partial class UCInventory : UserControl
    {
        private static MySqlConnection DBCon;
        private static MySqlCommand comm;
        private static MySqlDataReader reader;

        List<string> category_list = new List<string>();

        public UCInventory()
        {
            InitializeComponent();
            this.dataGridInventory.CellContentDoubleClick += new DataGridViewCellEventHandler(dataGridInventory_CellContentDoubleClick);
            DBCon = new MySqlConnection(Program.getConnection());
            qGetCategory();

            dataGridInventory.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;

        }

        private void calculatorToolStripMenuItem_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("calc");
        }

        public void qInventory()
        {
            string query = "SELECT i.item_id AS item_id, i.description AS name, i.actual_price AS actual_price, c.description AS category, SUM( iq.quantity ) AS quantity FROM ITEM AS i, ITEM_QUANTITY AS iq, CATEGORY AS c WHERE c.cat_id = i.cat_id AND i.item_id = iq.item_id AND quantity >0 GROUP BY i.item_id ORDER BY name";
            try
            {
                dataGridInventory.DataSource = Query.select(query);
            }
            catch (MySqlException exc) { MessageBox.Show(exc.Message); }
        }

        public void qGetCategory()
        {
            cbCategory.Items.Clear();
            cbCategory.Items.Add("All");
            string query = "SELECT description, cat_id FROM CATEGORY order by description";
            try
            {
                comm = new MySqlCommand(query, DBCon);
                DBCon.Open();
                reader = comm.ExecuteReader();
                {
                    while (reader.Read())
                    {
                        //adds the user to the combobox 
                        cbCategory.Items.Add(reader["description"].ToString());
                        category_list.Add(reader["cat_id"].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            DBCon.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string description = "", category = "";
            if (!cbCategory.Text.ToLower().Equals("all") && cbCategory.Text.Trim().Length > 0)
            {
                category = " AND c.description LIKE '%" + cbCategory.Text.Trim() + "%' ";
            }
            if (!tbItem.Text.Equals(""))
                description = " AND (i.item_id LIKE '%" + tbItem.Text + "%' OR i.description LIKE '%" + tbItem.Text + "%') ";
            string query = "SELECT i.item_id, i.description AS name, i.actual_price, c.description AS category, SUM( iq.quantity ) AS quantity " +
                            "FROM ITEM AS i, ITEM_QUANTITY AS iq, CATEGORY AS c WHERE quantity >0 AND c.cat_id = i.cat_id " +
                            "AND i.item_id = iq.item_id" + category + "" + description + " GROUP BY i.item_id ORDER BY i.description";
            try
            {

                dataGridInventory.DataSource = Query.select(query);
            }
            catch (MySqlException exc) { MessageBox.Show(exc.Message); }
        }

        private void dataGridInventory_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Singleton.showInvItems((int)dataGridInventory.Rows[e.RowIndex].Cells["item_id"].Value, (string)dataGridInventory.Rows[e.RowIndex].Cells["name"].Value, (double)dataGridInventory.Rows[e.RowIndex].Cells["actual_price"].Value);
        }
    }
}
